********************************************************************************
*                                                                              *
*                  CREATE WAGE AND FIRM CHARACTERISTICS 	     			   *
*               TO MERGE WITH FOR JOB REGISTER (BAANSOMMEN)                    *
*                                                                              *
********************************************************************************



// Directory
cd "H:\"
global yearlist 2000 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015 2016 

foreach year in $yearlist {
	if `year' == 2000 | `year' == 2001 | `year' == 2002 | `year' == 2003 | `year' == 2004 | `year' == 2005 | `year' == 2006 | `year' == 2008 | `year' == 2009 | `year'== 2010 | `year'== 2011 {
		import spss using "G:\Arbeid\BAANKENMERKENBUS\\`year'\140930 BAANKENMERKENBUS `year'V3.sav", case(lower) clear
	}
	if `year' == 2007 | `year' == 2012  {
		import spss using "G:\Arbeid\BAANKENMERKENBUS\\`year'\140930 BAANKENMERKENBUS `year'V2.sav", case(lower) clear
	}
	if `year' == 2013 | `year'==2015 {
		import spss using "G:\Arbeid\BAANKENMERKENBUS\\`year'\BAANKENMERKENBUS`year'V2.sav", case(lower) clear
	}
	if `year' == 2014 {
		import spss using "G:\Arbeid\BAANKENMERKENBUS\\`year'\BAANKENMERKEN`year'BUSV2.sav", case(lower) clear
	}
	if `year' == 2016 {
		import spss using "G:\Arbeid\BAANKENMERKENBUS\BAANKENMERKENBUS`year'V1.sav", case(lower) clear
	}

	
	keep arbeidsrelatiebaanid baanid beidbaanid autovandezaakbaanid caosectorbaanid sectbaanid

	g flexcontract = arbeidsrelatiebaanid=="2"
	g companycar = autovandezaakbaanid=="1"
	rename (caosectorbaanid sectbaanid) (caosector sect)
	destring caosector, force replace
	destring sect, force replace		
	drop arbeidsrelatiebaanid  autovandezaakbaanid
	rename beidbaanid beid
	destring beid, force replace
		
	drop if beid == . | caosector == . | sect == .
	
	duplicates drop baanid, force
	g year = `year'
	save "Robots and Workers\Data\temp_Baankenmerken_`year'.dta", replace
}

foreach year in $yearlist {
	if `year' == 2006 | `year' == 2007 | `year' == 2008 | `year' == 2009 | `year'== 2010 | `year'== 2011 | `year' == 2012 {
		import spss using "G:\Arbeid\BAANSOMMENTAB\\`year'\140930 BAANSOMMENTAB `year'V2.sav", case(lower) clear
	}
	if `year' == 2000 | `year' == 2001 | `year' == 2002 | `year' == 2003 | `year' == 2004 | `year' == 2005 {
		import spss using "G:\Arbeid\BAANSOMMENTAB\\`year'\140930 BAANSOMMENTAB `year'V3.sav", case(lower) clear
	}
	if `year' == 2013 {
		import spss using "G:\Arbeid\BAANSOMMENTAB\\`year'\BAANSOMMENTAB `year'V1.sav", case(lower) clear
	}
	if `year' == 2014  | `year'==2015 {
		import spss using "G:\Arbeid\BAANSOMMENTAB\\`year'\BAANSOMMEN`year'TABV1.sav", case(lower) clear
	}
	if `year' == 2016 {
		import spss using "G:\Arbeid\BAANSOMMENTAB\BAANSOMMEN`year'TABV1.sav", case(lower) clear
	}

	keep if rinpersoons == "R"
	drop rinpersoons
	g income_gross = fiscloon
	g hoursworked = (svdg*deeltijdfactorbaanid)*8
	g contracthours = (kaldg*(5/7)*deeltijdfactorbaanid)*8 - (25.6+7.5)*8*deeltijdfactorbaanid*(kaldg/365)  // Subtract average number of vacation-days and bank holidays
	g year = `year'
	merge m:1 baanid using "Robots and Workers\Data\temp_Baankenmerken_`year'.dta", nogen keep(1 3)
	keep rinpersoon beid baanid hoursworked contracthours income_gross flexcontract companycar
	g flexcontractXhoursworked = flexcontract*hoursworked
	g companycarXhoursworked = companycar*hoursworked
	
	// Keep baanid of job WITHIN THE FIRM that generates most hours
	bysort rinpersoon beid: egen maxhoursworked = max(hoursworked)
	replace baanid = "" if int(maxhoursworked)!=int(hoursworked)
	drop maxhoursworked
		
	collapse (sum) hoursworked contracthours income_gross flexcontractXhoursworked companycarXhoursworked (firstnm) baanid, by(rinpersoon beid)
	g flexcontract = flexcontractXhoursworked/hoursworked
	g companycar = companycarXhoursworked/hoursworked
	drop flexcontractXhoursworked companycarXhoursworked
	g hwage = income_gross/hoursworked
	foreach pctile in 10 20 30 40 50 60 70 80 90 {
		bysort beid: egen pctile_`pctile' = pctile(hwage), p(`pctile')
		if `pctile' == 10 {
			g hwage_`pctile' = hwage<=pctile_`pctile'
		}
		if `pctile' > 10 {
			local pctile_low = `pctile'-10
			g hwage_`pctile' = hwage<=pctile_`pctile' & hwage>pctile_`pctile_low'
		}
	}
	g hwage_pctile = 100*(hwage_10+hwage_20+hwage_30+hwage_40+hwage_50+hwage_60+hwage_70+ hwage_80+hwage_90 ==0)
	foreach pctile in 10 20 30 40 50 60 70 80 90 {
		replace hwage_pctile = hwage_`pctile'*`pctile' if hwage_pctile==0
		drop hwage_`pctile' pctile_`pctile'
	}
	g year = `year'
	do "Robots and Workers\Do-files\Variable labels"
	compress
	save "Robots and Workers\Data\Baansommen_`year'.dta", replace
	erase "Robots and Workers\Data\temp_Baankenmerken_`year'.dta"
}
	
	
	